from sqlalchemy import text
from venv import logger

from sqlalchemy.orm import Session

from core.entity.do.models import CmsGaodeFence, CmsCar
from core.entity.vo.view_models import CmsGaodeFenceVO, PaginationQuery, PaginationQueryForPage
from utils.uuid import UUID


class CmsGaodeFenceDao:
    """
    高德终端dao
    """

    @classmethod
    def get_cms_gaode_fence_by_id(cls, db: Session, id: str):
        """
        根据id查询高德终端信息
        :param db: orm对象
        :param id: 主键ID
        :return: 高德终端信息对象
        """
        cms_gaode_fence = db.query(CmsGaodeFence) \
            .filter(CmsGaodeFence.id == id) \
            .first()

        return cms_gaode_fence

    @classmethod
    def get_cms_gaode_fence_by_fence_id(cls, db: Session, fence_id: str):
        """
        根据终端id查询高德终端信息
        :param db: orm对象
        :param fence_id: 终端id
        :return: 高德终端信息对象
        """
        cms_gaode_fence = db.query(CmsGaodeFence) \
            .filter(CmsGaodeFence.fence_id == fence_id) \
            .first()

        return cms_gaode_fence

    @classmethod
    def get_cms_gaode_fence_like_name(cls, db: Session, fence_name: str):
        """
        获取所有的参数配置信息
        :param db: orm对象
        :param fence_name: 终端名称
        :return: 高德终端信息对象集合
        """
        cms_gaode_fence_list = db.query(CmsGaodeFence) \
            .filter(CmsGaodeFence.fence_name.like(f'%{fence_name}%') if fence_name else True) \
            .all()

        return cms_gaode_fence_list

    @classmethod
    def add_cms_gaode_fence_dao(cls, db: Session, cms_gaode_fence: CmsGaodeFenceVO):
        """
        新增高德终端
        :param db: orm对象
        :param cms_gaode_fence: 高德终端对象
        :return:
        """
        # Convert CmsGaodeFenceVO to dictionary and exclude the 'cars' field
        cms_gaode_fence.id = UUID().uuid()
        model_dict = cms_gaode_fence.dict(exclude={"cars", "car_numbers"})

        # Create CmsGaodeFence ORM model instance
        cms_gaode_fence_model = CmsGaodeFence(**model_dict)

        # Add and flush the new model instance to the database
        db.add(cms_gaode_fence_model)
        db.flush()

        return cms_gaode_fence_model

    @classmethod
    def edit_cms_gaode_fence_dao(cls, db: Session, cms_gaode_fence: dict):
        """
        编辑参数配置数据库操作
        :param db: orm对象
        :param cms_gaode_fence: 需要更新的参数配置字典
        :return:
        """
        db.query(CmsGaodeFence) \
            .filter(CmsGaodeFence.id == cms_gaode_fence.get('id')) \
            .update(cms_gaode_fence)

    @classmethod
    def delete_cms_gaode_fence_dao(cls, db: Session, id: int):
        """
        编辑参数配置数据库操作
        :param db: orm对象
        :param id: id
        :return:
        """
        db.query(CmsGaodeFence) \
            .filter(CmsGaodeFence.id == id) \
            .delete()

    @classmethod
    def get_fence_list(cls, db: Session, query: PaginationQuery):
        try:
            offset = (query.page_num - 1) * query.page_size
            limit = query.page_size
            key_word = query.key_word

            sql_text = """
                SELECT a.*, 
                       array_agg(b.terminal_id) AS cars,
                       array_agg(c.car_number) AS car_numbers
                FROM cms_gaode_fence a
                LEFT JOIN cms_gaode_fence_terminal b ON a.fence_id = b.fence_id
                LEFT JOIN cms_car c ON b.terminal_id = c.car_id
                
            """
            sql_text2 = """
                GROUP BY a.id
                ORDER BY a.id DESC
                LIMIT :limit OFFSET :offset
            """
            if query.key_word:
                sql_text = sql_text + (f"where c.car_number like '%{key_word}%' "
                                       f"or a.fence_name like '%{key_word}%'")
            sql_text = sql_text + sql_text2
            sql = text(
                sql_text
            )
            params = {
                "limit": limit,
                "offset": offset,
                "key_word": offset
            }

            result = db.execute(sql, params)
            fence_list = result.fetchall()
            # 组装数据
            fence_list_new = []
            for e in fence_list:
                vo = CmsGaodeFenceVO()
                vo.id = e.id
                vo.service_id = e.service_id
                vo.alarm_type = e.alarm_type
                vo.fence_id = e.fence_id
                vo.fence_type = e.fence_type
                vo.fence_name = e.fence_name
                vo.fence_desc = e.fence_desc
                vo.circle_center = e.circle_center
                vo.circle_radius = e.circle_radius
                vo.polygon_points = e.polygon_points
                vo.polyline_bufferradius = e.polyline_bufferradius
                vo.district_adcode = e.district_adcode
                vo.state = e.state
                vo.create_time = e.create_time
                vo.create_by = e.create_by
                vo.update_time = e.update_time
                vo.update_by = e.update_by
                vo.cars = e.cars
                vo.car_numbers = e.car_numbers
                fence_list_new.append(vo)
            count_sql = text("""
                 SELECT COUNT(*) FROM cms_gaode_fence
             """)
            total_result = db.execute(count_sql)
            total = total_result.scalar()

            return fence_list_new, total

        except Exception as e:
            logger.exception("Failed to fetch fences")
            raise e
